﻿using Hxj.Data;
using RecruitStudents.Client.DAL.Base;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Model;
using RecruitStudents.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RecruitStudents.Utility.Const;
using System.Data.Common;
using System.Data.SqlClient;

namespace RecruitStudents.Client.DAL
{
    public class DALAppointment : BaseService
    {
        public int GetAppointmentStudentCount(string strGroupNO, string examPlaceNo, string specialityKindsNo, string examLevel)
        {
            var where = TB_Exam_SignUpStudentsSpeciality._.ExamPlaceNO == examPlaceNo
                       && TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO == specialityKindsNo
                       && TB_Exam_SignUpStudentsSpeciality._.ExamLevel == examLevel
                       && TB_Exam_SignUpStudentsSpeciality._.IsActive == "1";
            switch (examLevel.ToSafeInt())
            {
                case ConstExamLevel.CS_Value:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First == strGroupNO);
                    break;
                case ConstExamLevel.FS_Value:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second == strGroupNO);
                    break;
                case ConstExamLevel.SS_Value:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third == strGroupNO);
                    break;
                case ConstExamLevel.SSS_Value:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fourth == strGroupNO);
                    break;
                case ConstExamLevel.WWS_Value:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth == strGroupNO);
                    break;
                case ConstExamLevel.LLS_Value:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth == strGroupNO);
                    break;
                default:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID.IsNull());
                    break;
            }
            return DbHelper.GetDatabase().From<TB_Exam_SignUpStudentsSpeciality>() //选择表
                                         .LeftJoin<TB_SignUpStudents>(TB_SignUpStudents._.SignUpID == TB_Exam_SignUpStudentsSpeciality._.SignUpID)
                                         .Where(where)   //条件
                                         .Count();
        }

        public DataTable GetAppointmentStudentDataTable(string strGroupNO, string examPlaceNo, string specialityKindsNo, string examLevel)
        {
            OrderByClip oder = null;

            var where = TB_Exam_SignUpStudentsSpeciality._.ExamPlaceNO == examPlaceNo
                       && TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO == specialityKindsNo
                       && TB_Exam_SignUpStudentsSpeciality._.ExamLevel == examLevel
                       //&& TB_Exam_SignUpStudentsSpeciality._.AllowExamID.IsNull("") != ""
                       && TB_Exam_SignUpStudentsSpeciality._.IsActive == "1";

            var selectField = new List<Hxj.Data.Field>();
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.All);
            selectField.Add(TB_SignUpStudents._.Name);
            selectField.Add(TB_SignUpStudents._.ExamProvince);
            selectField.Add(TB_SignUpStudents._.IdentityCard);
            selectField.Add(TB_SignUpStudents._.NMT_SignUpID);
            selectField.Add(TB_SignUpStudents._.LinkMobile);
            switch (examLevel.ToSafeInt())
            {

                case ConstExamLevel.CS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First.As("AppointmentGroupTime"));
                    oder = new OrderByClip(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First, OrderByOperater.ASC);
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First == strGroupNO);
                    break;
                case ConstExamLevel.FS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Second.As("AppointmentGroupTime"));
                    oder = new OrderByClip(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Second, OrderByOperater.ASC);
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second == strGroupNO);
                    break;
                case ConstExamLevel.SS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Third.As("AppointmentGroupTime"));
                    oder = new OrderByClip(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Third, OrderByOperater.ASC);
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third == strGroupNO);
                    break;
                case ConstExamLevel.SSS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fourth.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fourth.As("AppointmentGroupTime"));
                    oder = new OrderByClip(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fourth, OrderByOperater.ASC);
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fourth == strGroupNO);
                    break;
                case ConstExamLevel.WWS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fifth.As("AppointmentGroupTime"));
                    oder = new OrderByClip(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fifth, OrderByOperater.ASC);
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth == strGroupNO);
                    break;
                case ConstExamLevel.LLS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Sixth.As("AppointmentGroupTime"));
                    oder = new OrderByClip(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Sixth, OrderByOperater.ASC);
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth == strGroupNO);
                    break;
                default:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID.IsNull());
                    break;
            }

            return DbHelper.GetDatabase().From<TB_Exam_SignUpStudentsSpeciality>() //选择表
                                         .LeftJoin<TB_SignUpStudents>(TB_SignUpStudents._.SignUpID == TB_Exam_SignUpStudentsSpeciality._.SignUpID)
                                         .Select(selectField.ToArray())  //设置选择的字段数目 
                                         .Where(where)   //条件
                                         .OrderBy(oder)   //排序
                                         .ToDataTable();   //返回List
        }

        public DataTable CheckStudentAppointmentDataTable_New(string idCard, string examPlace, string specialityKindsNo, string examLevel, string strExamYear)
        {
            var where = TB_Exam_SignUpStudentsSpeciality._.ExamPlaceNO == examPlace
                         && TB_Exam_SignUpStudentsSpeciality._.IsActive == "1"
                         && TB_Exam_SignUpStudentsSpeciality._.ExamLevel == examLevel
                         && TB_Exam_SignUpStudentsSpeciality._.ExamYear == strExamYear
                         && "ISNULL(AllowExamID,'')='' "
                         && TB_SignUpStudents._.IdentityCard == idCard
                         && TB_SignUpStudents._.ExamYear == strExamYear;
            var selectField = new List<Hxj.Data.Field>();
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.All);
            selectField.Add(TB_SignUpStudents._.Name);
            selectField.Add(TB_SignUpStudents._.ExamProvince);
            selectField.Add(TB_SignUpStudents._.IdentityCard);
            selectField.Add(TB_SignUpStudents._.NMT_SignUpID);
            selectField.Add(TB_SignUpStudents._.LinkMobile);
            switch (examLevel.ToSafeInt())
            {
                case ConstExamLevel.CS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First.As("AppointmentGroupTime"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_First.As("IsFee"));
                    break;
                default:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID.IsNull());
                    break;
            }

            return DbHelper.GetDatabase().From<TB_Exam_SignUpStudentsSpeciality>() //选择表
                                         .LeftJoin<TB_SignUpStudents>(TB_SignUpStudents._.SignUpID == TB_Exam_SignUpStudentsSpeciality._.SignUpID)
                                         .Select(selectField.ToArray())  //设置选择的字段数目 
                                         .Where(where)   //条件
                                         .ToDataTable();   //返回List
            //var where = TB_Exam_SignUpStudentsSpeciality._.ExamPlaceNO == examPlace
            //           && TB_Exam_SignUpStudentsSpeciality._.IsActive == "1"
            //           && TB_SignUpStudents._.IdentityCard == idCard
            //           && TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO.Contain(specialityKindsNo);
            //var selectField = new List<Hxj.Data.Field>();
            //selectField.Add(TB_Exam_SignUpStudentsSpeciality._.All);
            //selectField.Add(TB_SignUpStudents._.Name);
            //selectField.Add(TB_SignUpStudents._.ExamProvince);
            //selectField.Add(TB_SignUpStudents._.IdentityCard);
            //selectField.Add(TB_SignUpStudents._.NMT_SignUpID);
            //selectField.Add(TB_SignUpStudents._.LinkMobile);
            //where = where.And(TB_Exam_SignUpStudentsSpeciality._.ExamLevel == examLevel);
            //switch (examLevel.ToSafeInt())
            //{
            //    case ConstExamLevel.CS_Value:
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First.As("AppointmentGroupTime"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_First.As("IsFee"));
            //        break;
            //    case ConstExamLevel.FS_Value:
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second.As("AppointmentGroup"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Second.As("AppointmentGroupTime"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Second.As("IsFee"));
            //        break;
            //    case ConstExamLevel.SS_Value:
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third.As("AppointmentGroup"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Third.As("AppointmentGroupTime"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Third.As("IsFee"));
            //        break;
            //    case ConstExamLevel.SSS_Value:
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fourth.As("AppointmentGroup"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fourth.As("AppointmentGroupTime"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Fourth.As("IsFee"));
            //        break;
            //    case ConstExamLevel.WWS_Value:
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth.As("AppointmentGroup"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fifth.As("AppointmentGroupTime"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Fifth.As("IsFee"));
            //        break;
            //    case ConstExamLevel.LLS_Value:
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth.As("AppointmentGroup"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Sixth.As("AppointmentGroupTime"));
            //        selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Sixth.As("IsFee"));
            //        break;
            //    default:
            //        where = where.And(TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID.IsNull());
            //        break;
            //}

            //return DbHelper.GetDatabase().From<TB_Exam_SignUpStudentsSpeciality>() //选择表
            //                             .LeftJoin<TB_SignUpStudents>(TB_SignUpStudents._.SignUpID == TB_Exam_SignUpStudentsSpeciality._.SignUpID)
            //                             .Select(selectField.ToArray())  //设置选择的字段数目 
            //                             .Where(where)   //条件
            //                             .ToDataTable();   //返回List
        }

        public DataSet GetStudentAppointmentDataTable_Print(string idCard, string examPlace, string specialityKindsNo, string examLevel, string strExamYear)
        {
            //            string strSql = @"select a.Sex,a.Name,a.ExamProvince,a.IdentityCard,a.NMT_SignUpID,a.LinkMobile,a.LinkMan,a.LinkPostalCode,a.LinkTelphone,a.CertificatePostAddress,a.Remark
            //,b.Photo,c.AllowExamID from TB_SignUpStudents as a left join TB_SignUpStudentsPhoto as b on a.SignUpID=b.SignUpStudentsID 
            //left join (select distinct SignUpID, AllowExamID from TB_Exam_SignUpStudentsSpeciality where ExamPlaceNO = '{0}' 
            //and ExamLevel = '{1}' and SpecialityKindsNO like '{2}%') as c on a.SignUpID = c.SignUpID where a.IdentityCard='{3}' 
            //";
            //            strSql = string.Format(strSql, examPlace, examLevel, specialityKindsNo, idCard);
            string strSql = @"select a.Sex,a.Name,a.ExamProvince,a.IdentityCard,a.NMT_SignUpID,a.LinkMobile,a.LinkMan,a.LinkPostalCode,a.LinkTelphone,a.CertificatePostAddress,a.Remark
                ,b.Photo,c.AllowExamID,c.SpecialityKindsName from TB_SignUpStudents as a left join TB_SignUpStudentsPhoto as b on a.SignUpID=b.SignUpStudentsID 
                left join (select distinct SignUpID, AllowExamID,b.SpecialityKindsNameAS as SpecialityKindsName from TB_Exam_SignUpStudentsSpeciality as a left join TB_Sys_SpecialityKinds as b
                on a.SpecialityKindsNO=b.SpecialityKindsNO where ISNULL(a.AllowExamID,'')<>'' and a.ExamPlaceNO = '{0}' and a.ExamLevel = '{1}' and a.ExamYear='{3}') as c 
                on a.SignUpID = c.SignUpID where ISNULL(c.AllowExamID,'')<>'' and  a.IdentityCard='{2}'  and a.ExamYear='{3}'
            ";
            strSql = string.Format(strSql, examPlace, examLevel, idCard, strExamYear);
            return DbHelper.GetDatabase().FromSql(strSql).ToDataSet();
        }

        public DataTable CheckStudentAppointmentDataTable(string idCard, string examPlace, string specialityKindsNo, string examLevel)
        {
            var where = TB_Exam_SignUpStudentsSpeciality._.ExamPlaceNO == examPlace
                       && TB_Exam_SignUpStudentsSpeciality._.SpecialityKindsNO == specialityKindsNo
                       && TB_Exam_SignUpStudentsSpeciality._.IsActive == "1"
                       && TB_SignUpStudents._.IdentityCard == idCard;
            var selectField = new List<Hxj.Data.Field>();
            selectField.Add(TB_Exam_SignUpStudentsSpeciality._.All);
            //ExamProvince,Name,IdentityCard,NMT_SignUpID,LinkMobile
            selectField.Add(TB_SignUpStudents._.Name);
            selectField.Add(TB_SignUpStudents._.ExamProvince);
            selectField.Add(TB_SignUpStudents._.IdentityCard);
            selectField.Add(TB_SignUpStudents._.NMT_SignUpID);
            selectField.Add(TB_SignUpStudents._.LinkMobile);
            where = where.And(TB_Exam_SignUpStudentsSpeciality._.ExamLevel == examLevel);
            switch (examLevel.ToSafeInt())
            {
                case ConstExamLevel.CS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_First.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_First.As("AppointmentGroupTime"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_First.As("IsFee"));
                    break;
                case ConstExamLevel.FS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Second.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Second.As("AppointmentGroupTime"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Second.As("IsFee"));
                    break;
                case ConstExamLevel.SS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Third.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Third.As("AppointmentGroupTime"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Third.As("IsFee"));
                    break;
                case ConstExamLevel.SSS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fourth.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fourth.As("AppointmentGroupTime"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Fourth.As("IsFee"));
                    break;
                case ConstExamLevel.WWS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Fifth.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Fifth.As("AppointmentGroupTime"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Fifth.As("IsFee"));
                    break;
                case ConstExamLevel.LLS_Value:
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroup_Sixth.As("AppointmentGroup"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.AppointmentGroupTime_Sixth.As("AppointmentGroupTime"));
                    selectField.Add(TB_Exam_SignUpStudentsSpeciality._.IsFee_Sixth.As("IsFee"));
                    break;
                default:
                    where = where.And(TB_Exam_SignUpStudentsSpeciality._.SignUpStudentsSpecialityID.IsNull());
                    break;
            }

            return DbHelper.GetDatabase().From<TB_Exam_SignUpStudentsSpeciality>() //选择表
                                         .LeftJoin<TB_SignUpStudents>(TB_SignUpStudents._.SignUpID == TB_Exam_SignUpStudentsSpeciality._.SignUpID)
                                         .Select(selectField.ToArray())  //设置选择的字段数目 
                                         .Where(where)   //条件
                                         .ToDataTable();   //返回List
        }

        /// <summary>
        /// 根据考点和专业生成准考证号
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <param name="strSpecialityKindsNO"></param>
        /// <returns></returns>
        public string CreateExamAllowExamID(string strExamPlaceID, string strSpecialityKindsNO, string strExamYear)
        {
            string strAppNo = CreateExamAllowExamIdData(strExamPlaceID, strSpecialityKindsNO, strExamYear);
            strExamYear = strExamYear.Substring(strExamYear.Length - 2);
            if (strExamPlaceID.Length > 2)
            {
                strExamPlaceID = strExamPlaceID.Substring(0, 2);
            }
            strAppNo = strExamYear + strExamPlaceID + strSpecialityKindsNO + strAppNo.PadLeft(4, '0');
            return strAppNo;
        }

        /// <summary>
        /// 根据考点和专业生成准考证号
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <param name="strSpecialityKindsNO"></param>
        /// <returns></returns>
        public string CreateExamAllowExamIdData(string strExamPlaceID, string strSpecialityKindsNO, string strExamYear)
        {
            string strValue = string.Empty;
            DbParameter[] parameters = new DbParameter[]
                {
                    new SqlParameter("@ExamPlaceNO", SqlDbType.NVarChar,50),
                    new SqlParameter("@SpecialityKindsNO",SqlDbType.NVarChar,50),
                    new SqlParameter("@ExamYear", SqlDbType.NVarChar,50)
                };
            parameters[0].Value = strExamPlaceID;
            parameters[1].Value = strSpecialityKindsNO;
            parameters[2].Value = strExamYear;
            strValue = DbHelper.GetDatabase().FromProc("PRO_CreateExamAppNo").AddParameter(parameters).ToScalar<string>();
            return strValue;
        }
    }
}
